import pymysql
import config


class MyDatabase:
    def __init__(self):
        try:
            self.conn = pymysql.Connect(
                user='root',
                password='123456',
                database='mydb',
                host='localhost',
                port=3306,
                charset='utf8'
            )
        except Exception:
            print("database connection fail")
            pass


mydb = None


def find_data_by_dataset_name(dataset_id):
    global mydb
    if mydb is None:
        mydb = MyDatabase()
    filenames = []
    labels = []
    cur = mydb.conn.cursor()
    strcur = "SELECT data_id from data_from WHERE dataset_id=" + str(dataset_id) + ";"
    cur.execute(strcur)
    data_id = cur.fetchall()
    for idid in data_id:
        strcur = "SELECT filename, label from data_file WHERE id=" + str(idid[0]) + ";"
        cur.execute(strcur)
        ifn, ilb = cur.fetchone()
        filenames.append(config.get_data_path(ifn))
        labels.append(ilb)
    return filenames, labels



def insert_model(model_info: list):
    global mydb
    if mydb is None:
        mydb = MyDatabase()
    str_model_info = [
        'name', 'dataset_id', 'train_ratio', 'valid_ratio',
        'epochs', 'batch_size', 'learning_rate', 'optimizer_name', 'accuracy'
    ]
    cur = mydb.conn.cursor()
    strcur = "INSERT INTO model (" + ', '.join(str_model_info) + ')'
    strcur += ' VALUES ('
    model_info = ['"' + x + '"' if type(x) is str else str(x) for x in model_info]
    strcur += ', '.join(model_info) + ');'
    print(strcur)
    cur.execute(strcur)
    mydb.conn.commit()

# print(find_data_by_dataset_name('testdb'))
# insert_model(['test2', 1, 0.34, 0.34, 10, 32, 0.005, "Adam", 0.936])
# print(find_model_by_model_name('test'))
